﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data.Sql;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;

namespace RecursosHumanos
{
    public class BI
    {
        public static string RetornaConsulta(BIRelatorio relatorio)
        {
            var bd = new WBGEntities();

            string campos = String.Empty, camposAgrup = String.Empty, where = String.Empty, having = string.Empty;
            string campoTMP = String.Empty, whereTMP = string.Empty;
            bool usaAgrup = false, usaCamposComum = false;

            try
            {
                foreach (BIRelatorioCampo campo in bd.BIRelatorioCampo.Where(x => x.BIRelatorio.idRelatorio == relatorio.idRelatorio))
                {
                    if (campo.ativo == true && campo.exibir)
                    {
                        //if (campo.exibir == true)
                        //{
                        campos += campos.CompareTo(String.Empty) == 0 ? "" : ", ";
                        if (campo.contar == true)
                        {
                            campoTMP = "COUNT(1) ";
                            campos += campo + "[" + campo.BIModeloCampo.rotulo + "]";
                        }
                        if (campo.somar == true)
                        {
                            campoTMP = "SUM(" + campo.BIModeloCampo.nome + ") ";
                            campos += campoTMP + "[" + campo.BIModeloCampo.rotulo + "]";
                        }
                        if (campo.media == true)
                        {
                            campoTMP = "AVG(" + campo.BIModeloCampo.nome + ") ";
                            campos += campoTMP + "[" + campo.BIModeloCampo.rotulo + "]";
                        }
                        if (campo.contar || campo.somar || campo.media)
                        {
                            usaAgrup = true;
                        }
                        else
                        {
                            usaCamposComum = true;
                            campoTMP = campo.BIModeloCampo.nome;
                            campos += campoTMP + " " + "[" + campo.BIModeloCampo.rotulo + "]";

                            camposAgrup += camposAgrup.CompareTo(String.Empty) == 0 ? "" : ", ";
                            camposAgrup = campoTMP;
                        }
                        //}
                        if (campo.filtro != String.Empty)
                        {
                            whereTMP = campos.CompareTo(String.Empty) == 0 ? "" : " AND ";
                            whereTMP += campoTMP + FormataFiltro(campo.tipoFiltro.Value, campo.filtro);
                            if (campo.contar || campo.somar || campo.media)
                            {
                                having += whereTMP;
                            }
                            else
                            {
                                where += whereTMP;
                            }
                        }
                    }
                }
            }
            catch
            {
            }
            finally
            {
                if (bd != null)
                    bd.Dispose();
            }

            return "SELECT "+ campos +
                    " FROM "+ relatorio.BIModeloTabela.nome + 
                    (where.CompareTo(String.Empty) == 0 ? "" : " WHERE "+ where) + 
                    (having.CompareTo(String.Empty) == 0 ? "" : " HAVING "+ having) +
                    (usaAgrup && usaCamposComum ? " GROUP BY "+ camposAgrup : "");
        }

        public static DataTable RetornaDataTable(BIRelatorio relatorio)
        {
            DataTable dataTable = null;
            SqlDataAdapter dataAdapter = null;
            string connectionStr = ConfigurationManager.ConnectionStrings["WBGEntities"].ConnectionString;
            int pos1 = connectionStr.IndexOf("data source");
            int pos2 = connectionStr.IndexOf(";App");
            connectionStr = connectionStr.Substring(pos1, pos2 - pos1);
            SqlConnection connection = null;

            try
            {
                dataTable = new DataTable();
                dataAdapter = new SqlDataAdapter();
                connection = new SqlConnection(connectionStr);
                dataAdapter.SelectCommand = new SqlCommand(RetornaConsulta(relatorio), connection);
                dataAdapter.Fill(dataTable);
            }
            catch (Exception ex)
            {
                throw;
            }
            finally
            {
                if(dataAdapter != null)
                    dataAdapter.Dispose();
                if(connection != null)
                    connection.Dispose();
                if(dataTable != null)
                    dataTable.Dispose();
            }
            return dataTable;
        }

        private static string FormataFiltro(byte tipoFiltro, string filtro)
        {
            string[] x;
            filtro = filtro.Replace(",", "','");
            switch (tipoFiltro)
            {
                case 1: case 3:
                    return " IN ('"+ filtro +"')";
                case 2: case 4:
                    return " NOT IN ('"+ filtro +"')";
                case 5:
                    x = filtro.Split('-');
                    if (x.Count() != 2)
                        throw new Exception("Filtro de intervalo inválido");
                    return " BETWEEN '" + x[0] + "' AND '" + x[1] +"'";
                case 6:
                    x = filtro.Split('-');
                    if (x.Count() != 2)
                        throw new Exception("Filtro de intervalo inválido");
                    return " NOT BETWEEN '" + x[0] + "' AND '" + x[1] + "'";
                case 7:
                    return " > " + filtro + "'";
                case 8:
                    return " < " + filtro + "'";
                default:
                    throw new Exception("Tipo de filtro inválido.");
            }
        }
    }
}
